package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import vo.BoardVO;
import vo.ListVO;
import vo.MemberVO;

public class BoardDAO {
	private static BoardDAO boardDAO = new BoardDAO();
	private static Logger logger = Logger.getLogger(BoardDAO.class);
	private BoardDAO() {

	}

	public static BoardDAO getInstance() {
		return boardDAO;
	}	
	
	public static Connection conUtil(Connection con) throws SQLException {
		String URL = "jdbc:oracle:thin:@localhost:1521:xe";
		String USER = "insoo";
		String PASS = "1234";
		con = DriverManager.getConnection(URL, USER, PASS);
		return con;
	}
	
	public void closeAll(Connection con, PreparedStatement pstmt) {
		if(pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void closeAll(Connection con, PreparedStatement pstmt, ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		closeAll(con, pstmt);
	}
	
	/**
	 * 로그인 처리
	 * @param vo
	 * @return boolean
	 * true - 로그인 성공, false - 로그인 실패
	 */
	public boolean loginCheck(MemberVO vo) {
		logger.debug("params: " + vo);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean flag = false;
		
		String sql =	"SELECT userid " +
						" FROM board_user " +
						" WHERE userid = ? AND userpass = ?";
		
		logger.debug("SQL: " + sql);
		
		String userid = vo.getUserid();
		String userpass = vo.getUserpass();
		try {
			pstmt = conUtil(con).prepareStatement(sql);
			pstmt.setString(1, userid);
			pstmt.setString(2, userpass);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(con, pstmt, rs);
		}
		return flag;
	}
	
	/**
	 * 회원가입
	 * @param vo
	 * @return
	 */
	public int addUserInfo(MemberVO vo) {
		logger.debug("params: " + vo);
		Connection con = null;
		PreparedStatement pstmt = null;
		int result = 0;
		
		String sql =	"INSERT INTO board_user(userid, userpass, username, useraddr) " +
						" VALUES(?, ?, ?, ?)";
		logger.debug("SQL: " + sql);
		
		try {
			pstmt = conUtil(con).prepareStatement(sql);
			pstmt.setString(1,	vo.getUserid());
			pstmt.setString(2,	vo.getUserpass());
			pstmt.setString(3,	vo.getUsername());
			pstmt.setString(4,	vo.getUseraddr());
			result = pstmt.executeUpdate();	
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(con, pstmt);
		}
		return result;
	}
	
	/**
	 * 새글 작성
	 * @param vo
	 * @return
	 */
	public int addPost(BoardVO vo) {
		logger.debug("params: " + vo);
		Connection con = null;
		PreparedStatement pstmt = null;
		int result = 0;
		
		String sql = "INSERT INTO board_list (brdno, brduser, brdtitle, brdcontent)" +
				" VALUES(brdseq.nextVal, ?, ?, ?)";
		logger.debug("SQL: " + sql);
		
		try {
			pstmt = conUtil(con).prepareStatement(sql);
			pstmt.setString(1, vo.getBrdUser());
			pstmt.setString(2, vo.getBrdTitle());
			pstmt.setString(3, vo.getBrdContent());
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(con, pstmt);
		}
		
		return result;
	}
	
	/**
	 * 리스트 출력
	 * @return
	 */
	public ListVO getBoardList() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		ListVO lvo= new ListVO();
		List<BoardVO> list = new ArrayList<BoardVO>();
		
		
		String sql = "SELECT brdno, brduser, brdtitle, TO_CHAR(brddate, 'MM-DD') brddate, brdcnt" +
				" FROM board_list ORDER BY brdno desc";
		logger.debug("SQL: " + sql);
		try {
			pstmt = conUtil(con).prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				BoardVO bvo = new BoardVO();
				bvo.setBrdNo(rs.getInt(1));
				bvo.setBrdUser(rs.getString(2));
				bvo.setBrdTitle(rs.getString(3));
				bvo.setBrdDate(rs.getString(4));
				bvo.setBrdCnt(rs.getInt(5));
				logger.debug(bvo);
				list.add(bvo);
			}
			lvo.setBoardList(list);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(con, pstmt, rs);
		}
		return lvo;
	}
	
	/**
	 * 글 갯수 리턴
	 * @return
	 */
	public int getPostCount() {
		int postCount = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT count(brdno) FROM board_list";
		logger.debug("SQL: " + sql);
				
		try {
			pstmt = conUtil(con).prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				postCount = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(con, pstmt, rs);
		}
		return postCount;
	}
	
	/**
	 * 글 읽기
	 * @return
	 */
	public BoardVO getPost(BoardVO bvo) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = "SELECT brduser, brdtitle, TO_CHAR(brddate, 'YYYY-MM-DD') brddate, (brdcnt + 1) brdcnt, brdcontent" +
				" FROM board_list" +
				" WHERE brdno = ?";
		logger.debug("SQL: " + sql);
		try {
			pstmt = conUtil(con).prepareStatement(sql);
			pstmt.setInt(1, bvo.getBrdNo());
			rs = pstmt.executeQuery();
			if(rs.next()) {
				bvo.setBrdUser(rs.getString(1));
				bvo.setBrdTitle(rs.getString(2));
				bvo.setBrdDate(rs.getString(3));
				bvo.setBrdCnt(rs.getInt(4));
				bvo.setBrdContent(rs.getString(5));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(con, pstmt, rs);
		}
		
		return bvo;
	}
	
	/**
	 * 카운터수 +1
	 */
	public void setPostCount(BoardVO bvo) {
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = "UPDATE board_list SET brdcnt = (brdcnt+1) WHERE brdno = ?";
		logger.debug("SQL: " + sql);
		logger.debug("params: " + bvo);
		try {
			pstmt = conUtil(con).prepareStatement(sql);
			pstmt.setInt(1, bvo.getBrdNo());
			int result = pstmt.executeUpdate();
			logger.debug("update: " + result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}
